SQL in InfoAsset Manager

In its simplest form, SQL may be used to selected objects for which an expression is true. What this means exactly will be handled in a later section, but for now we will confine ourselves to uncontroversial arithmetic and string comparisons.

The available arithmetic operators are:

+	Addition
-	Subtraction
*	Multiplication
/	Division
^	Exponent (e.g. width^2, area^0.5)
%	Modulus

It is also possible to use '-' to negate a number, this is known as 'unary minus'.

These arithmetic operators follow the normal operator precedence, with unary minus having highest precedence, then exponent, then division and multiplication, then finally addition and subtraction.

For example a * b + c means multiply a and b, then add c. You can use brackets to override these precedence rules e.g. a * (b + c) means add b and c then multiply by a. There is nothing to stop you from using brackets where they are not needed in order to clarify your intention. There is no harm in writing (a * b) + c instead of a * b + c.

You can also use the addition operator '+' to join strings. For example, if user text 1 were 'X12' and user text 2 were 'Y34' then user_text_1 + user_text_2 would yield 'X12Y34'.

None of the other operators listed above have any special meaning for strings. The behaviour if you do use one of these operators with two strings, or with a string and a number etc. will be described in detail in a later section.

You can use comparison operators to compare values to see if they are equal, one is greater than the other etc.

The available comparison operators are:

=	Equality
>=	Greater than or equal to
>	Strictly greater than
<=	Less than or equal to
<	Strictly less than
<>	Not equal to

The comparison operators have a higher precedence than the arithmetic operators. This means that if you say something like a + b * c >= d + e * f , the left and right hand sides of the comparison operator are evaluated, then the comparison is done.

There are three things to note about string comparison in InfoAsset Manager:

1. String comparison is always case insensitive.
2. When using less than, greater than, greater than or equal to and less than or equal to, the behaviour will depend on the behaviour of the language in which you are using Windows.
3. Strings are always trimmed so that they have no leading or trailing spaces or 'tab' characters i.e. a string can never begin with a string or a tab.

Having described the arithmetic and comparison operators, the next question that will spring to mind is what can be manipulated with the arithmetic operators and what can be compared.

We will start by considering two things: fields and constants. Other things, including variables, will be explained in a later section.

Fields can be found in the pull down lists on the SQL dialog. The fields fall into a number of categories:

1. Fields in the current table e.g. node_id, us_node_id, x, y, width, height
2. Fields in joined tables. These fall into two distinct categories:
   1. One-to-one links – links where there can be only one object linked – e.g. the link from a link to its upstream node is a one-to-one link since any given link can only be linked to one (or zero) upstream node
   2. One-to-many links – links where there can be more than one object linked – e.g. the link from a node to links upstream of it is a one-to-many link since any given node can have zero, one, two or more links upstream of it.
3. Fields in 'structure blobs' e.g. details in CCTV surveys

As well as these four main categories, there are some special cases which will be described later in this paper.

The fields in the current table have the meaning you would expect. The others will be explained in later sections.

You may wonder what happens when you have selected more than one table in the pull-down list of tables e.g. 'All Nodes', 'All Links', and a given object is of a type that does not have a particular field e.g. if the object for which the query is being evaluated is a conduit and the field is specific to a flap valve. This will be explained in the next section once the special value used has been described.

Constants fall into 5 types:

1. Numbers – simply put the number in, with or without decimal points and the minus sign e.g. 123, -123, 123.45, -123.45.
2. Strings – strings should begin and end with the single quote character ' e.g. 'mynodeid' or the double quote character e.g. "mynodeid". If you begin a string with one of these you must end it with the same one. To include the single quote character within a string, use the double quotes at the beginning and the end of the string. To include the double quote character within a string, use the single quotes at the beginning and the end of the string .
3. Boolean – The Boolean data type and the two constant values will be described later in this paper.
4. Dates – Date constants begin and end with the # character. The rules are as follows:
   1. Behaviour depends on the ‘locale’ setting if your PC
   2. If you are in the UK or another country using dates of the form dd/mm/yyyy then dates should be entered #day/month/year# e.g. #31/1/2008#.
   3. If you are in the US or another country using dates of the form mm/dd/yyyy then dates should be entered #month/day/year# e.g. #1/31/2008#.
   4. If you are in Japan, China or another country using dates of the form yyyy/mm/dd then dates should be entered #2008/31/1# .
   5. It is possible, but not recommended to enter dates without using all 4 digits in the year (e.g. #1/31/08#). The behaviour of 2 digit years is determined by the regional and language options control panel – see http://support.microsoft.com/kb/214391 for details.
   6. It is also possible but also not recommended to enter dates of only two parts e.g. #1/12#. In both of these cases the underlying date library will resolve this as described in the above Microsoft knowledge base article.
   7. If you are using dates of the form dd/mm/yyyy or mm/dd/yyyy and you enter a date which is invalid in the form you are using, but valid in the other of these two forms, the date will be interpreted as the other e.g. if you are in the US and enter #31/1/2008#, this is an invalid date since there is no 31st month, however it would be valid as a UK date, so it is treated as being the 31st of January 2008
5. NULL – this is a special value, described in the next section of this paper.

In InfoAsset Manager, numerical fields can usually be blank. In SQL this is represented as a special value called 'NULL', and the fields are said to 'be NULL'. You can see if fields are / aren’t NULL by using the special constructs 'IS NULL' and 'IS NOT NULL' e.g. ground_level IS NULL, ground_level IS NOT NULL.

We are now in the position to answer the question posed above, as to what value is returned for objects that do not have a particular field when the query is being evaluated for a number of different types of object e.g. 'All Links'. The answer is simply that the NULL value is returned for any object that does not have the field in question.

You can combine the expressions built up so far with the logical operators AND, OR and NOT e.g.

x>10000 AND y>20000 will select nodes where the x coordinate is greater than 10000 AND the y coordinate is greater than 20000.

x>10000 AND y>20000 will select nodes where the x coordinate is greater than 10000 AND the y coordinate is greater than 20000.

As with all implementations of SQL and in common usage for programming languages 'A or B' means A is true, B is true OR both A and B are true. This is often referred to as 'inclusive or', and is slightly different from the way in which the word 'or' is sometimes used in day to day English. When the keyword NOT is used it will select objects for which the expression to which the NOT applies is not true.

The keyword AND has higher precedence than the keyword OR, so A OR B AND C OR D means the same thing as (A OR B) AND (C OR D). As with the other operators described above it is possible to override this precedence rule by using brackets, so you can say things like A OR (B AND C) OR D.

There are two operators which can be used to perform more sophisticated tests on strings than the tests described above. These operators are LIKE and MATCHES.

LIKE may be used to perform relatively simple tests on strings by testing for prefixes and a match in the number of characters in a string. This is done by using the special characters ? and *. ? matches any character * matches the rest of the string.

Any other character matches only that character itself – in common with the string comparisons described above this matching is case insensitive.

e.g.

node_id LIKE 'MH12345' will match only the string MH12345, since no special characters are included in the string on the right of the LIKE operator.

node_id LIKE '????????' will match any 8 character node ID

node_id LIKE 'MH??????' will match any 8 character string beginning MH

node_id LIKE '??1????' will match any node ID with 1 as the 3rd character

node_id LIKE 'MH*' will match any node ID beginning with MH

node_id LIKE 'MH??*' will match any node ID at least 4 characters long beginning with MH.

You cannot check for anything after * has been used in the pattern on the right of the LIKE operator, e.g. node_id LIKE '*01' cannot be used to match any node ID ending in 01. Similarly you cannot use * at the beginning and end to match something in the middle, so you cannot say node_id LIKE '*01*' to match any node ID with 01 somewhere in the middle.

MATCHES may be used to perform complex tests on strings by seeing if they match regular expressions.

A full description of regular expressions falls outside the scope of this paper. Note:

1. The regular expression matching, in common with the string comparisons described above and the LIKE keyword, is case insensitive.
2. The regular expression much match the whole of the string i.e. node_id MATCHES '[0-9]*' will only match node_ids containing only digits.

These results will appear in the list box for the object type in the SQL dialog prefixed with sim.

It is also possible to load the results of a second simulation by using the appropriate menu item. The results for this simulation will then also appear in the list box for the object type in the SQL dialog prefixed with sim2.

These results will be the results at the current timestep, or the maximum results if the time control has been set to show the maximum results.

It is also possible to use simulation results for all timesteps by using the prefixes tsr and tsr2, in which tsr stands for time series results.

It is possible to include values for linked objects in SQL expressions. As described above these fall into two categories, at this stage we are only considering links where an object is linked to ONE other object (or possibly no object) of a particular type. If you are familiar with SQL in databases such as Microsoft Access, Oracle, MySQL etc. you may be familiar with this concept as being a 'join'. However, whereas in these packages you will have to join the table to the other table by means of a view or the JOIN keyword, in InfoAsset Manager, the joining is normally done for you automatically, taking advantage of the software packages knowledge of their own data structures. These are sometimes referred to as 'implicit joins'.

The implicit one-to-one joins within the same InfoAsset Manager Network are as follows:

 

InfoAsset Manager – Collection Network

From	To	Name
Acoustic Survey	Pipe	pipe
Assets	General Maintenance	general_maintenance_records
	General Survey	general_surveys
	General Survey Line	general_survey_lines
	Tasks	tasks
	User Defined Task Only	user_tasks
	Water Quality Survey	water_quality_surveys
	Work Package	work_packages
Blockage Incident	Pipe	joined
	Property	property
	Node	node
CCTV Survey	Pipe	joined
Channel	Cross Section Survey	cross_section_surveys
Collapse Incident	Node	node
	Pipe	joined
	Property	property
Complaint Incident	Node	node
	Pipe	joined
	Property	property
Cross Section Survey	Channel	channel
Defense Structure	Flood Defence Survey	flood_defence_surveys
Drain Test	Storm Manhole	storm_manhole
	Property	property
	Sanitary Manhole	sanitary_manhole
Dye Test	Pipe	joined
Flood Defence Survey	Defence Structure	defence_structure
Flooding Incident	Node	node
	Pipe	joined
	Property	property
General Incident	Node	node
	Pipe	joined
	Property	property
GPS Survey	Manhole	joined
Link	Node	ds_node
	Node	us_node
Manhole Repair	Manhole	joined
Manhole Survey	Manhole	joined
Materials	Orders	orders
Monitoring Survey	Pipe	joined
	Data Logger	data_logger
	Node	node
Name Group	Node/Pipe/Connection Pipe	asset
Node	Pipe	lateral_pipe
Node/Pipe/Connection Pipe	Name Group	asset_name_groups
Odor Incident	Property	property
	Node	node
	Pipe	joined
Pipe	Acoustic Survey	acoustic_surveys
Pipe Clean	Pipe	joined
Pipe Repair	CCTV Survey	review_cctv_survey
	Pipe	joined
Pipe/Connection Pipe/Pipe Repair	CCTV Survey	cctv_survey
Pollution Incident	Property	property
	Node	node
	Pipe	joined
Property	FOG Inspection	fog_inspections
	Storm Pipe	storm_pipe
	Sanitary Pipe	sanitary_pipe
Pump	Treatment Works	treatment_works
Pump Station	Electrical Maintenance	electrical_maintenance_records
	Generator	generators
	Mechanical Maintenance	mechanical_maintenance_records
	Pump	pumps
	Pump Station Survey	pump_station_surveys
Pump/Generator	Pump Station	pump_station
Smoke Defect	Smoke Test	joined
Smoke Test	Pipe	joined
Tasks	Resource	resources
	Work Package	work_package
Tasks/Incidents	Connection Pipe	connection_pipe
Tasks/Orders	Material	materials
Work Package	Asset	asset
	Tasks	tasks

InfoAsset Manager – Distribution Network

From	To	Name
Assets	General Maintenance	general_maintenance_records
	General Survey	general_surveys
	General Survey Line	general_survey_lines
	Tasks	tasks
	User Defined Task Only	user_tasks
	Water Quality Survey	water_quality_surveys
	Work Package	work_packages
Burst Incident	Pipe	joined
	Node	node
Complaint Incident	Node	node
	Pipe	joined
General Incident	Node	node
	Pipe	joined
GPS Survey	Node	joined
Hydrant	Hydrant Test	hydrant_test
Hydrant Maintenance	Hydrant	hydrant
Hydrant/Meter/Valve Maintenance	Valve Shutoff	valve_shut_off
Link	Node	ds_node
Link	Node	us_node
Manhole Repair	Manhole	joined
Manhole Survey	Manhole	joined
Manhole Survey/Repair	Manhole	manhole
Materials	Orders	orders
Meter	Meter Maintenance	meter_maintenance
	Meter Test	meter_test
Meter Maintenance/Test	Meter	meter
Monitoring Survey	Node	joined
Node	Pipe	lateral_pipe
Node/Pipe	Leak Detection	leak_detections
Pipe Repair	Pipe	joined
Pipe Sample	Pipe	joined
Property	Meter	meter
Pump	Treatment Works	treatment_works
Pump Station	Electrical Maintenance	electrical_maintenance_records
	Generator	generators
	Mechanical Maintenance	mechanical_maintenance_records
	Pump	pumps
	Pump Station Survey	pump_station_surveys
Pump/Generator	Pump Station	pump_station
Tasks	Resource	resources
	Work Package	work_package
Tasks/Orders	Material	materials
Valve	Valve Maintenance	valve_maintenance
	Valve Shutoff	valve_shut_offs
Valve Maintenance	Valve	valve
Valve Shutoff	Hydrant Maintenance	hydrant_maintenance_records
	Meter Maintenance	meter_maintenance_records
	Valve	valves
	Valve Maintenance	valve_maintenance_records
Water Quality Incident	Node	node
	Pipe	joined
Work Package	Asset	asset
	Tasks	tasks

As you can see, the 'joined' prefix has a number of different meanings depending on what is being joined. The 'joined' prefix is used in exactly those circumstances where the fields of the joined object appear in the grids in the software to the right of the object and in italics.

It is possible to select objects in a number of clauses which are evaluated one after another. The clauses should be separated with a semi-colon e.g. x>10000; y>20000; ground_level > 15.5

Once you have more than one clause, the ‘test’ button on the SQL dialog will no longer tell you how many objects will be selected. Instead, the ‘test’ button will confirm that the query has the correct syntax.

Each clause will be executed in turn, and the objects for which the expression in the clause is true will be added to the selection.

In this case it would be possible to write the query equivalently as x>10000 OR y>20000 OR ground_level > 15.5

As you will see, however, there are more complex things that can be done with multiple clauses that cannot be reframed as a single query.

The first example of something that can be done with multiple clauses that cannot be done with a single query is the selection of objects in multiple tables. Whilst an SQL query is for a particular table or group of tables e.g. Node, Pipe, All Nodes, All Links. This is referred to as the 'default' table, and this paper will refer to it as A table, even though it is possible to select things from more than one tables using 'All Nodes', 'All Links' etc.

It is possible to override that table by explicitly naming the table or tables in the clause. This is done by using the 'SELECT FROM' keyword e.g. if the default table is Node and you want to select all conduits with width greater than 100 mm you should say SELECT FROM Conduit WHERE width > 100.

In the unlikely event that you wish to select all conduits you can omit the keyword WHERE and the expression after it and simply say SELECT FROM Conduit. If the table name you wish to use contains spaces in it, it is necessary to put square brackets before and after the name e.g. SELECT FROM [All Links] WHERE width > 100. It is in fact possible to enclose any table name within square brackets so you can in fact say SELECT FROM [Conduit] WHERE width > 100.

The check-box on the SQL dialog determines whether the query is applied to all objects or to the currently selected objects.

If the check box is unchecked then the query will be applied to all objects of the appropriate type, if it is checked then the query will only be applied to the currently selected objects of that type. This is referred to as the 'default selection behaviour'.

This behaviour can be overridden by using the keywords ALL or SELECTED. ALL will override the behaviour of the check-box to make the query be run for all objects of the appropriate type, whereas SELECTED will override the behaviour of the check-box to make the query be run for only the selected objects of the appropriate type.

If you are using the default table then you can override the behaviour by saying SELECT ALL WHERE followed by the expression or SELECT SELECTED WHERE followed by the expression e.g. SELECT ALL WHERE x > 10000, SELECT SELECTED WHERE y > 20000.

If you are overriding the table name as described above, you should add the keyword ALL or SELECTED between the keywords SELECT and FROM e.g. SELECT ALL FROM [All Nodes] WHERE x > 20000, SELECT SELECTED FROM Conduit WHERE width > 200.

In the unlikely event you wish to select all objects in the default table you can say SELECT ALL. Similarly if you want to select all objects of another type you can say SELECT ALL FROM followed by the table name e.g. SELECT ALL FROM Conduit.

In addition to selecting objects, it is possible to deselect objects i.e. make objects that were selected unselected. This is done by using the keyword DESELECT, usually followed by the keyword WHERE followed by an expression e.g. DESELECT WHERE x > 10000. Any expression that can be used to select objects can be used to deselect objects.

It is possible to override the default table as described above by saying DESELECT FROM followed by the table name, usually followed by the keyword WHERE followed by an expression e.g. DESELECT FROM [All Links] WHERE width > 200.

If you wish to deselect all objects from the default table you can say DESELECT. Similarly if you want to deselect all objects from another table you can say DESELECT FROM followed by the table name e.g. DESELECT ALL FROM Conduit.

It is in fact possible to use the extra keywords ALL and SELECTED as described above but in practice they are not required, since deselecting all objects and deselecting selected objects have the same effect.

As well as selecting and deselecting objects it is possible to clear the entire selection. This is done using the keywords CLEAR SELECTION on their own as a separate clause. This has a different effect from saying DESELECT or DESELECT FROM followed by a table name as described above, since they remove objects of a particular type or types from the selection whereas CLEAR SELECTION clears the selection completely.

It is possible to select all objects in the default table by saying SELECT. This also works on multiple tables if the default table is something like 'all nodes' or 'all links'.

There are numerous other ways of selecting all objects which rely on the fact that when the expression is run, all objects for which the expression is true will be selected

e.g.

1, true, 'anystring'

As described above it is possible to select all objects in a table other than the default table by saying SELECT FROM followed by the table name e.g. SELECT FROM [All Nodes].

These queries will only change the selection if the default selection behaviour is to select all objects rather than to apply the query to the current selection, otherwise you are simply selecting all the objects that are already selected.

If you want to explicitly override the default selection behaviour you should say SELECT ALL or SELECT ALL followed by the table name.

As described above it is also possible to deselect all objects in the default table or another table.

Please click here for more details.

YEARPART

YEARPART(date)

Given a date, returns the year of the date as a number.

MONTHPART

MONTHPART(date)

Given a date, returns the month in the date as a number.

DAYPART

DAYPART(date)

Given a date, returns the day of the date as a number.

DATEPART

DATEPART(date)

Given a date, returns the date part as a date i.e. removes any minutes from the date so the value represents midnight at the beginning of that day.

TIMEPART

TIMEPART(date)

Given a date, returns the time part as a number in minutes e.g. if the date is 01/02/2008 12:34 this returns 754.0 – the number of minutes after midnight.

YEARMONTHPART

YEARMONTHPART(date)

Given a date, returns the year and month part as a string: yyyy/mm. If the month is January - September a 0 prefix is applied, e.g. 2006/02.

MONTHYEARPART

MONTHYEARPART(date)

Given a date, returns the month and year part as a string: mm/yyyy. If the month is January - September a 0 prefix is applied, e.g. 02/2006

MONTHNAME

MONTHNAME(date)

Given a date, returns the month part as the full name of the month (in the current locale)

SHORTMONTHNAME

SHORTMONTHNAME(date)

Given a date, returns the month part as the abbreviated name of the month (as determined by the locale and how Windows abbreviates it)

DAYNAME

DAYNAME(date)

Given a date, returns the day part as the full name of the day (in the current locale)

SHORTDAYNAME

SHORTDAYNAME(date)

Given a date, returns the day part as the abbreviated name of the day (as determined by the locale and how Windows abbreviates it)

NOW

NOW()

Returns the current date and times as a date.

YEARSDIFF

YEARSDIFF(from,to)

Returns the number of complete years between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved i.e. if from is 01/05/2008 at 12:34 and ‘to’ is 01/05/2009 at 12:29, this will count as a full year and therefore return 1.

MONTHSDIFF

MONTHSDIFF(from,to)

Returns the number of complete months between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved.

DAYSDIFF

DAYSDIFF(from,to)

Returns the number of complete days between the two dates as a number, which will be an integer. The calculation is based on midnight on the dates involved.

INYEAR

INYEAR(date,number)

Returns true if the date is in the year given as a number e.g.

INYEAR(when_surveyed,1993)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

INMONTH

INMONTH(date,month,year)

Returns true if the date is in the year and month given as numbers e.g.

INMONTH(when_surveyed,3,1993)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

INYEARS

INYEARS(date,startyear,endyear)

Returns true if the date is in a year between the start and end years inclusive e.g.

INYEARS(when_surveyed,1993,1995)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

INMONTHS

INMONTHS(date,startmonth,startyear,endmonth,endyear)

Returns true if the date is between the start month in the start year and the end month in the end year inclusive e.g.

INMONTHS(when_surveyed,10,1993,2,1994)

If any of the parameters to the functions are not numbers the function returns false. If the number is not an integer, the number is rounded to the nearest integer.

ISDATE

ISDATE(putative_date)

If the field is a date because it has come from the database returns true, if it is a string then returns true if it can be converted into a date, otherwise returns false.

NUMTOMONTHNAME

NUMTOMONTHNAME(n)

Returns the month name given an integer from 1 to 12.

NUMTOSHORTMONTHNAME

NUMTOSHORTMONTHNAME(n)

Returns the short version of a month name (e.g. Jan) given an integer from 1 to 12.

TODATE

TODATE(year,month,day)

Returns the date given the year, month and day as integers.

TODATETIME

TODATETIME(year,month,day,hours,minutes)

Returns the date given the year, month, day, hours and minute as integers.

DATEFORMAT

DATEFORMAT(date,dateformat)

Given a 'date' value, this formats the date part of it according to the second parameter. If the first parameter is not a date or the second is not a string then null is returned.

This uses the Win32 API GetDateFormat function, so you can search for more details concerning e.g. the behaviour in other languages.

d	day of the month without leading zeros for single-digit days
dd	day of the month with leading zeros for single-digit days
ddd	short / abbreviated day of the week e.g. 'Mon' in English
dddd	day of the week in full e.g. 'Monday'
M	month as digits without leading zeros for single-digit days
MM	month as digits with leading zeros for single-digit days
MMM	short / abbreviated month e.g. 'Jan' in English
MMMM	month in full e.g. 'January'
y	last digit of year
yy	last two digits of the year with leading zero for single-digit years
yyyy	full year
g / gg	the era e.g. A.D. / B.C. in English

 

TIMEFORMAT

TIMEFORMAT(date,timeformat)

Given a 'date' value, this formats the time part of it according to the second parameter. If the first parameter is not a date or the second is not a string then null is returned.

This uses the Win32 API GetTimeFormat function, so you can search for more details concerning e.g. the behaviour in other languages.

h	hours with no leading zero for single-digits hours in 12 hour clock
hh	hours with leading zero for single-digits hours in 12 hour clock
H	hours with no leading zero for single-digits hours in 24 hour clock
HH	hours with leading zero for single-digits hours in 24 hour clock
m	minutes with no leading zero for single digit minutes
mm	minutes with leading zero for single digit minutes
s	seconds with no leading zero for single digit minutes
ss	seconds with leading zero for single digit minutes
t	A or P for AM or PM
tt	AM or PM

These characters are case sensitive. Any other character is passed through to the output unchanged. If you were to put more than 2 of these letters consecutively they are treated as being the same as 2 of that letter.

DATETIMEFORMAT

DATETIMEFORMAT(date,dateformat,timeformat)

Given a date and a date format and a time format, this returns a string consisting of the date part of the 'date' value formatted as it would be by DATEFORMAT, followed by a space, followed by the time part of the 'date' value formatted as it would be by TIMEFORMAT.

DAYOFWEEK

DAYOFWEEK(date)

Given a date, returns the day-of-the-week of the date as a number according to the ISO8601 standard (from 1=Monday to 7=Sunday).

DAYOFYEAR

DAYOFYEAR(date)

Given a date, returns the day-of-the-year of the date as a number (1=January 1st).

DAYSINYEAR

DAYSINYEAR(date)

Given a date, returns the number of days in the year of the date.

Please refer to the List Variables section.

Trigonometric functions are functions of an angle.

All these functions return NULL if their parameters cannot be converted into numbers. There may be further restrictions on the parameters as described below.

Please note that all angles in the trigonometric functions are expressed in degrees.

ACOS

ACOS(x)

Calculates the inverse cosine of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range 0 to 180 degrees.

ASIN

ASIN(x)

Calculates the inverse sin of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range -90 degrees to 90 degrees.

ATAN

ATAN(x)

Calculates the inverse tangent of x. Returns a value within the range -90 degrees to 90 degrees.

ATAN2

ATAN2(x,y)

Calculates the inverse tangent of x / y using the signs of x and y to correctly determine the quadrant. Returns a value within the range -180 degrees to 180 degrees.

COS

COS(x)

Calculates the cosine of x.

EXP

EXP(x)

Calculates e^x.

GAMMALN

Calculates the natural logarithm of the gamma function, ?(x). Returns NULL if x<=0.

LOG

LOG(x)

Calculates the log (base 10) of x. Returns NULL if x cannot be converted into a number, or x<=0.

LOGE

LOGE(x)

Calculates the log (base e) of x, otherwise known as a natural logarithm. Returns NULL if x<=0.

SIN

SIN(x)

Calculates the sin of x.

TAN

TAN(x)

Calculates the tangent of x. Returns NULL if cos(x) = 0 (and therefore tan(x) would be infinite).

LEN

LEN(string)

Returns the length of a string i.e. how many characters long the string is, so LEN('MYNODEID’' would be 8. This is often used in conjunction with other string manipulation functions described below. The function LEN may also be used to find the length of a list variable. List variables and the meaning of the LEN function in that context are described later in the paper.

LEFT

LEFT(string,number of characters)

Returns a string containing the first n characters in a string, where n is the second parameter passed to the function e.g. LEFT('MX11112222',2) would be 'MX'. If the second parameter is zero or less, the empty string ‘’ is returned. On the other hand, if the number is equal to or greater than the number of characters in the string the whole string is returned.

RIGHT

RIGHT(string, number of characters)

Returns a string containing the last n characters in a string, where n is the second parameter passed to the function e.g. RIGHT('MX11112222') would be '11112222'. If the second parameters is zero or less, the empty string ‘’ is returned. On the other hand, if the number is equal to or greater than the number of characters in the string the whole string is returned.

MID

MID(string,start position, number of characters)

Returns a string containing a number of characters from a string starting at a given position. The positions start from 1 for the first character, with 2 for the second etc. so MID('MX11112222',1,2) giving 'MX', MID('MX11112222',3,4) giving '1111' and MID('MX11112222',7,4) giving '2222'.

If the second parameter is 0 or less or greater than the number of characters in the string then the empty string is returned. If the third parameter is 0 or less than the empty string is returned. If the number of characters given in the third parameter is greater than the number of characters remaining starting from the second parameter, then the rest of the string is returned e.g. MID('MX12345678',9,999) would return '78', in this case the rest of the string starting from the 9th character.

SUBST

SUBST(string,thing to replace, thing to replace it with)

Replaces the first instance of the second parameter in the first with the third e.g. SUBST('01880132','01','ND') returns 'ND880132'

GSUBST

GSUBST(string, thing to replace, thing to replace it with)

Replaces all instances of the second parameter in the first with the third e.g. GSUBST('01880132','01','ND') returns 'ND88ND32'

GENSUBST

GENSUBST(string, regexp, format)

Replaces the string with the format defined if regular expression regxp matches the string, otherwise returns the string unchanged. e.g.

SET user_text_1 = GENSUBST(node_id,'SK([0-9]*)','99\1') will set user_text_1 to the node_id unless the node_id matches the regular expression 'SK([0-9]*)', otherwise will set user_text_1 to 99 followed by the numerical part of the node_id following the SK.

The regular expression may contain a number of bracketed sub-expressions. In this case there is one bracketed sub-expression ([0-9]*). The bracketing does not change what the regular expression will match, so 'SK([0-9]*)' will match the same things as will 'SK[0-9]*', however each sub-expression is given a number, starting with 1, and the text that matches that sub-expression may be used in the format by using that number preceded with a backslash. In this case what is happening is that a node ID consisting of the characters SK followed by a number of digits is replaced in the result of the function with the digits 99 followed by the string of digits from the node_id e.g. SK12345678 becomes 9912345678.

NL

NL()

The NL function returns a new-line character e.g. to set up a 3 line note field use something like

SET notes='THIS'+NL()+'THAT'+NL()+'THE OTHER'

FIXED

FIXED(number to convert,number of decimal places)

Given a number and a number of decimal places from 0 to 8, convert the number to a string with that number of decimal places, rounding up or down as appropriate. If the number of decimal places is 0 the string will have no decimal point.

e.g.

FIXED(1.9,3) is "1.900"

FIXED(1.9991,3) is "1.999"

FIXED(1.9999,3) is "2.000"

ABS

ABS(number)

Calculates the absolute value of a number i.e. if the number is less than zero returns negative 1 times the number, turning it positive e.g. ABS(-3) = 3. If the number is zero or above it is unchanged.

e.g. SET user_number_1 = ABS(ground_level)

INT

INT(number)

INT takes the integer part of a number. If the number is an integer it will remain unchanged.

FLOOR

FLOOR(number)

FLOOR returns the closest integer less than or equal to its parameter. Like INT it leaves integers unchanged. The difference between INT and FLOOR is that, whilst they will return the same value for positive numbers, INT will, by removing the fractional part of a number, make negative numbers larger (e.g. INT(-123.45) will be 123). FLOOR, on the other hand will make negative numbers more negative (e.g. FLOOR(-123.45) will be –124.

CEIL

CEIL(number)

CEIL returns the closed integer greater than or equal to its parameter. Like INT it leaves integers unchanged.

IIF

IIF(condition,first alternative, second alternative)

The IIF function returns the value of the second parameter if the first expression evaluates as true, otherwise returns the third. e.g.

IIF(service_total_score>total_score,service_total_score,total_score)>20 will select those objects with the maximum of the service_total_score and the total_score being greater than 20

NVL

NVL(expr1,expr2)

The NVL function returns the value of expr2 if expr1 is null. If expr1 is not null, then NVL returns expr1.

Scalar variables are variables that have a single value, rather than a value for every object of a particular type, they can be numbers, dates or strings, and are defined using the LET statement e.g.

LET $flag = "XP"

LET $threshold = 123.4

LET $date_threshold = #01-Jan-2003”

The values assigned to the variables have to be single values, they cannot be the results of expressions e.g. you cannot say LET $diameter = $area / $pi

SCALARS

The keyword SCALARS on its own will display all the scalars

SCALARS followed by a comma separated list of scalar variables will display just those variables e.g.

SCALARS $x, $z

Selection into scalar variables

If you have a SELECT clause which acts as a 'group by' for the entire table, it is possible to store the results in scalar variables e.g.

SELECT COUNT(*) INTO $mycount; SELECT(COUNT(x>0)) INTO $positivexcount

Scalar Expressions

It is possible to use the LET keyword to perform calculations on scalar variables as well as assign values. This is most likely to be useful when performing calculations on variables which have been set by select clauses as described above.

List variables are used in conjunction with a number of functions, known as 'list variable functions'.

List variables are defined by a special clause with the format

LIST variable_name = list of values (separated by commas) e.g.

LIST $widths = 100, 300, 500, 700, 900

LIST $codes = 'AB', 'AF', 'BC', 'BD'

The variable name must be valid as described above, beginning with the dollar sign. The equals sign must be there, as must the commas between the individual values. As well as numbers and strings it is possible to have lists of dates, which as described above must begin and end with the # character.

It is possible to declare a list variable but not to set any values by saying

LIST $widths

LIST $codes STRING

LIST $calendar DATE

These declare lists of numbers, strings and dates respectively.

There are four functions associated with list variables. In all cases the final parameter of the function must be a list variable and only a list variable. In addition, the LEN function may be used to find the length of a list variable. There are no other circumstances in which list variables may be used within SQL expressions.

One of the functions may only be used if the list is sorted, i.e. the values in the list must be strictly increasing, with each value being strictly greater than the previous value. In the case of numbers and dates ‘increasing’ means what you would expect. In the case of strings, it means that the strings are in alphabetical order, however that is defined for the language in which you are running Windows.

LEN

LEN(list variable)

The function LEN may be used to find the length of a list variable, that is to say the number of items in the list. e.g. LEN($widths) with $widths as defined above would return 5.

RINDEX

RINDEX(expression,list variable)

RINDEX is the function that may only be used if the list is sorted. The purpose of the RINDEX function is essentially to divide values into ‘buckets’. It returns 0 if the result of the expression is less than the first value in the list, 1 if it greater than or equal to the first value in the list but less than the second value in the list, 2 if it is greater than or equal to the second value in the list but less than the third value etc.

If there are n items in the list and the result of the expression is greater than or equal to the final item in the list hen RINDEX will return n.

LOOKUP

LOOKUP(expression,list variable)

If there are n items in the list and the expression is between 1 and n inclusive, LOOKUP will return the appropriate item from the list e.g. if the result of the expression is 1 it will return the first item, if it is 2 it will return the 2nd item, if it is n it will return the nth and final item. If the value is not an integer or an integer not in the range between 1 and n inclusive, it will return NULL.

LOOKUPFN

LOOKUPFN(expression, clamp option, step option, list variable)

Implements a stepwise or piecewise-linear x->y lookup function with a NULL or numeric input expression (x) and returns a NULL or numeric value (y).

The function is specified by the list variable, which must be a number list containing a sequence of x,y pairs in monotonic ascending order of x, with a minimum of one such pair. If step option is true, the function is evaluated stepwise, otherwise it is evaluated as piecewise linear. If x is NULL, y is NULL. If the clamp option is true, x is clamped to the range of the function before evaluating y. Otherwise if x is outside the range of the function, y is NULL.

MEMBER

MEMBER(expression, list variable)

MEMBER will return the Boolean value true if the result of the expression is one of the values in the list, false otherwise.

INDEX

INDEX(expression, list variable)

If the result of the expression is the first value in the list, INDEX will return 1, if the result is the second value in the list it will return 2 and so on. If the result of the expression is not in the list INDEX returns 0.

AREF

AREF(n,list)

Given a list variable list and a number from 1 to the length of list returns the nth element in the list e.g.

LIST $listname = 'one','two','three';

AREF(2,$listname) will return 'two'.

Table variables are used in GROUP BY clauses, described later. They are used when the results of the GROUP BY clause are needed in an implicit join, also described later.

SAVE $var(,$var,$var...) TO FILE 'filename'

SAVE $var(,$var,$var...) TO FILE $variable

LOAD $var(,$var,$var...) FROM FILE 'filename'

LOAD $var(,$var,$var...) FROM FILE $variable

SAVE ALL TO FILE 'filename'

SAVE ALL TO FILE $variable

Saves and loads a list of scalar and list variables into a file, or loads them from a file. The filename may be a string or a scalar variable containing a string.

It is possible to save all variables, but not load all variables.

All variables must have been defined beforehand e.g.

LET $x = null;

LIST $x;

etc.

This is currently the only way in which list variables can be changed.

e.g.

LET $x = 1.345;

LET $y = 'one two three';

LIST $xl = 1,23,456,7890;

LIST $yl = 'one','two','three','four,five';

LIST $zl = 'a','bb','ccc','ddd','eee','fff';

SAVE $x,$y,$xl,$yl,$zl TO FILE 'd:\temp\filename.dat'

You can find if there are any data in the array by using the function ANY with the parameter consisting of the name of the array followed by '.*' e.g. ANY(details.*). This will return true if there are any records in the array, false otherwise.

The precise nature of the function ANY will be described in a later part of this section.

You can count the number of rows in the array by using the function COUNT with the parameter consisting of the name of the array followed by '.*) e.g. COUNT(details.*).

For example, to select any CCTV survey records with more than 10 detail records you can say COUNT(details.*)>10.

To list the number of survey flags, S1, on nodes in the network:

SELECT node_id, COUNT(flags.value='S1')

The precise nature of the function COUNT will be described in a later part of this section.

There are a number of functions which, when applied to array fields, evaluate an expression for each row in the array field and then perform an action with all the results. The results of these functions can then be used in the same way as any other function when the expression is evaluated for the object with the array field.

What an aggregate function is will become clearer when they are described below:

ANY

The aggregate function ANY returns true if the expression is true for any row of the array field e.g. ANY(details.code='JDS') will return true if any of the rows of the details array field has the code JDS, false otherwise.

It is important to release that the expression within the brackets can contain more than one array field, other fields of the object, constants and non-aggregate functions, and these can all be combined in the same ways as before i.e. with arithmetic, comparison and logical operators, so it is possible to say things like ANY(details.code='ST' AND details.distance>0), which would detect objects where the details code is ST and the distance is greater than 0, which we might consider an error as ST is the code for a 'start' detail record and therefore the associated distance should be 0.

As you can see from this example, the expression within the brackets is evaluated for each record independently so this means 'are there any details records in which the details code is 'ST' and the distance is greater than 0' not 'are there any detail records in which the details code is 'ST' and also detail records where the distance is greater than 0. If this were what was desired this could be achieved by saying ANY(details.code='ST') AND ANY(details.distance>0).

ALL

The aggregate function ALL returns true if the expression is true for all rows of the array field e.g. ALL(details.distance>=0) will return true if all the rows of the details array field have the distance greater than or equal to zero, false otherwise.

COUNT

The aggregate function COUNT returns the number of records in the array field for which the expression is true e.g. COUNT(details.code=’ST’) returns the number of records in the array field for which the details code is ST.

MAX

This returns the maximum value for the expression for any of the records in the array field. Only non-NULL values will be considered.

MAX and MIN both work on numerical, date and string fields. In the case of string fields the comparison between strings is performed based on the language in which your Windows installation is set up.

MIN

This returns the minimum value for the expression for any of the records in the array field.

AVG

This returns the average of the value for the expression for all of the records in the array field. The average is calculated by dividing the total of the sum of all non-NULL values by the number of non-NULL values. If there are no non-NULL values AVG will return NULL.

FIRST and LAST

FIRST and LAST are included here with the aggregate functions but work somewhat differently, they return the value for the expression calculated for the first and last records of the array field respectively. Thus, in calculating the value, one of the records in the array field is considered.

Each aggregate function can contain fields from only one array field, e.g. you can't combine fields from pipes_in and pipes_out within a manhole survey. It is, however, possible to include multiple aggregate functions within an expression, with each aggregate function containing an expression referring to a different aggregate function. e.g. COUNT(pipes_in.*)>0 AND COUNT(pipes_out.*)>0 will select all manhole surveys with both pipes and and pipes out.

There is another use of aggregate functions which will be described later in this paper.

As well as using the values in array fields within the aggregate functions described above, it is possible to use the array fields outside these functions. When they are used in expressions the expression is evaluated for each record of the array field contained in it. It is only possible to include one array field in any expression although it is possible to include multiple references to the same array field in one.

details.code='GP' will select all CCTV surveys with at least one detail record with the code GP.

It is important to understand exactly what you are asking for in a query, particularly if the query includes tests other than equality e.g. saying details.code<>'GP' will be true if any of the records in the array field have a code other than GP. This will almost certainly not be what you want, and certainly does not mean 'any object where none of the records of the details array field has the value GP'.

It is possible to set values in array fields by using them in an assignment clause outside an aggregate function.

If there is only a SET sub-clause with no WHERE sub-clause, the SET sub-clause will be run for every record in the array field for every object in the table e.g. SET pipes_in.width = 123 will set the width for every ‘pipe in’ record in every manhole survey to 123.

If there is a WHERE sub-clause that does not include a reference to the array field, the SET sub-clause will be run for every record in the array field for every object for which the WHERE sub-clause is true e.g.

SET pipes_in.width = 234 WHERE shaft_depth = 1650 will set the width for every 'pipe in' record to 234 in every manhole survey where the shaft depth is 1650.

If there is a WHERE sub-clause that includes a reference to the array field, then the SET sub-clause will be run for every record in the array field for which the WHERE sub-clause is true.

If you use array field values outside aggregate functions in a WHERE sub-clause, and inside aggregate functions in the SET sub-clause, the aggregate functions in the SET sub-clause will only be evaluated for records in the array field for which the WHERE sub-clause is true.

This means that if you say

SET user_number_1 = COUNT(details.*) WHERE details.code = 'DES'this will set user_number_1 to the number of details records for the CCTV survey for which the code is DES. In this case saying SET user_number_1 = COUNT(details.code=’DES’) would have the same effect.

A more practical example is that to sum the service score for detail records of type DE could be done with the query

SET user_number_1 = SUM(details.service_score) WHERE details.code='DE'

An alternative way of calculating this without a bare array field would be SET user_number_1 = SUM(IIF(details.code='DE',details.service_score,0))

If, however, you use an aggregate function IN the WHERE sub-clause, the aggregate function will be run for all records of the array field.

ANY(linkname.*) may be used to determine if there are any linked objects e.g. ANY(us_links.*)

COUNT(linkname.*) may be used to count the number of linked objects e.g COUNT(manhole_surveys.*)=0

The aggregate functions listed above for array fields with the exception of FIRST and LAST are available for one-to-many links.

It is possible to set values in one-to-many fields by using them in an assignment clause outside an aggregate function.

If there is only a SET sub-clause with no WHERE sub-clause, the SET sub-clause will be run for every object related to every object in the table by the one to many link e.g.

SET us_pipes.width = 123 will set the width for every upstream pipe.

If there is a WHERE sub-clause that does not include a reference to the one-to-many field, the SET sub-clause will be run for every record in the one-to-many for every object for which the WHERE sub-clause is true e.g.

SET us_links.width = 234 WHERE shaft_depth = 1650 will set the width for every upstream link to 234 where the shaft depth is 1650.

If there is a WHERE sub-clause that includes a reference to the one-to-many field, then the SET sub-clause will be run for every object linked via the one-to-many field for which the WHERE sub-clause is true.

If you use one-to-many field values outside aggregate functions in a WHERE sub-clause, and inside aggregate functions in the SET sub-clause, the aggregate functions in the SET sub-clause will only be evaluated for objects linked to the current object via the one-to-many field for which the WHERE sub-clause is true.

If, however, you use an aggregate function IN the WHERE sub-clause, the aggregate function will be run for all objects linked to the current object via the one-to-many link.

An explicit select clause may be used to generate grids or CSV files containing one or more values for each object meeting given criteria.

The main advantage of using an explicit select clause over other ways of displaying data in the software is that you can display the results of calculations, including calculations on one-to-many links and array fields.

An explicit select clause consists of the following sub-clauses, most of which are optional.

1. A SELECT sub-clause containing the expressions to be output to file or grids within the software. The SELECT sub-clause is compulsory. There can be one or more expressions separated by commas, these are termed ‘sub-expressions’
2. A FROM sub-clause specifying the table from which the data should be extracted. This sub-clause is optional.
3. An INTO sub-clause specifying the file into which the results should be output. This sub-clause is optional.
4. A WHERE sub-clause specifying a condition which should be fulfilled before the object will be considered and included in the grid or file.

If no FROM sub-clause is specified, the current table will be used.

If no INTO sub-clause is specified, the results will be displayed in a grid.

If no WHERE sub-clause is specified, all the relevant objects will be considered.

The keyword DISTINCT can be used with SELECT to select a group of values without duplicates e.g.

SELECT DISTINCT system_type

will display a grid of each system type in the table.

DISTINCT can be used in conjunction with WHERE and ORDER BY, but not GROUP BY, HAVING, TOP or BOTTOM.

Where multiple fields are selected, the query returns one line for each distinct set of values, rather than the distinct values for each field.

SELECT distinct width,ds_width WHERE pipe_material = 'co' ORDER BY width ASC,ds_width ASC

The 'Group By' clause consists of the following sub-clauses, most of which are optional.

1. A SELECT sub-clause, as described above. As with the explicit select clause, this is compulsory.
2. A FROM sub-clause, as described above. This sub-clause is optional.
3. An INTO sub-clause, as described above. This sub-clause is optional.
4. A WHERE sub-clause, as described above. This sub-clause is optional.
5. A GROUP BY sub-clause listing the fields, expressions, variables etc. used to group the objects. There can be one or more fields, expressions etc. separated by commas, these are termed ‘sub-expressions’.
6. A HAVING sub-clause specifying a condition for the group (rather than the object) which should be fulfilled for the data for this group to be output.

If no FROM sub-clause is specified, the current table will be used.

If no INTO sub-clause is specified, the results will be output to a grid within the software.

If no WHERE sub-clause is specified, all the objects will be considered.

If no GROUP BY sub-clause is specified, all the objects in the table will be considered as one group (this is known as an ‘implicit GROUP BY’)

If no HAVING sub-clause is specified, all the groups will be output to the file or grid.

It is possible to use a blob field in a GROUP BY query e.g.

SELECT SUM(COUNT(details.*)) GROUP BY details.code

When the result of the GROUP BY clause is output to a file, the values for all the sub-expressions in the GROUP BY sub-clause and the SELECT sub-clause will be output to the file. The records output will be sorted in an order determined by the sub-expressions in the GROUP BY clause.

It is possible to alter the text used in the header of the CSV file or the grid for each expression within the SELECT sub-clause by using the keyword AS e.g.

SELECT COUNT(*) AS MyCount, MAX(x) AS MaxX Group BY status

Note that the aliases are not strings, they do not appear in quotation marks. If you want to have spaces in the names you should deal with them as with variables with spaces in the name and use square brackets e.g. [My Title].

The aggregate functions described above, with the exception of FIRST and LAST may be used in the sub-expressions in the SELECT sub-clause. It is also possible to use the special sub-expression COUNT(*), which will return the number of objects in each group.

It is possible to write a query which works like a GROUP BY clause but instead of grouping things by one or more fields or expressions, considers all the objects at once. A simple example of this would be a query that counts the number of objects in the table e.g. SELECT COUNT(*)

This is different from an explicit select clause e.g. SELECT node_id

The rule for determining whether a query is an implicit group by or a select acting on individual objects is as follows:

1. If there are no aggregate functions e.g. SELECT node_id,x,y then the query is clearly NOT an implicit GROUP BY
2. If there are aggregate functions acting on the results of aggregate functions e.g. SUM(COUNT(details.*)) then it clearly IS an implicit GROUP BY
3. If neither of the above apply then more detailed analysis has to be performed on each aggregate function as follows
   1. If the aggregate function contains an array field or a one-to-many link followed by an asterisk e.g. SUM(details.*), ANY(us_links.*), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.
   2. If the aggregate function contains a field for an array field or a one-to-many link followed by an asterisk e.g. COUNT(details.code=’Z’), MAX(us_links.width<150), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.

To sort in ascending or descending order, use the keyworkds ASC and DESC respectively. If neither keyword is specified, the sorting is ascending by default.

SELECT node_id, ground_level ORDER BY ground_level ASC

It is possible to sort based on more than one expression e.g. to sort by ground level and then by x coordinate in ascending order:

SELECT node_id, ground_level ORDER BY ground_level ASC, x ASC

For real numbers, sorting is by display precision (default 2) e.g.

SELECT node_id, ground_level,x ORDER BY ground_level DP 0 ASC, x ASC

SELECT node_id,ground_level,x ORDER BY ground_level DP 6 ASC, x ASC

To restrict the number of results, use the keywords TOP and BOTTOM.

The number of results can be limited to a given number of items and to a percentage. You can select the top or bottom from the results, and use a variable or a number.

The percentage is calculated as the percentage of the objects to which the query is applied e.g. if you have 100 nodes, but only 10 are selected at the point the query is run, and either the keyword SELECTED is used or the 'apply to current selection' check box is checked, then SELECT TOP 50 PERCENT will select 5 objects, not 50. Similarly, if there is a where clause, then the percentage applies tot he number of objects for which the WHERE clause is true.

SELECT TOP 5 node_id, ground_level ORDER BY ground_level DESC

SELECT BOTTOM 1.5 PERCENT node_id, ground_level ORDER BY ground_level DESC

Use the keywords WITH TIES to extend a selection to include all objects with the same value in the field being 'ordered by' as the last object selected. e.g.

SELECT TOP 10 WITH TIES ORDER BY ground_level DESC

The keywords WITHOUT TIES may be used, but are not necessary as this is the default behaviour.

It is also possible to use the keywords TOP and BOTTOM to restrict the number of objects deselected, deleted or that have values set. e.g.

DESELECT TOP 5 ORDER BY ground_level DESC

DELETE TOP 5 ORDER BY ground_level DESC

UPDATE TOP 10 SET user_number_1 = 123 ORDER BY ground_level DESC

It is possible to use the 'rank' of an object, where the rank is the position that the object appears in the list. Rank can only be used where there is an ORDER BY clause and can only appear on the right hand side in an assignment in a SET clause e.g.

SET user_number_1 = rank ORDER BY ground_level DESC

If there are tied values in the sort order, i.e. two objects have the same value in all the sort fields, then all objects with the same value will be given equal rank e.g.

SET user_number_1 = rank ORDER BY ground_level,chamber_roof

will result in objects with equal ground level and chamber roof level being given the same rank.

When using results in this manner the following aggregate functions may be used:

ALL

Returns true if the expression is true for all timesteps under consideration.

ALL (tsr.depth2d > 1.0)

will return true if the depth is greater than 1 for all timesteps under consideration.

ANY

Returns true if the expression is true for at least one of the timesteps under consideration.

AVG

AVG does not simply return an average of the values at all timesteps as length of timesteps may vary. AVG returns the time weighted average i.e. the sum of the values for all timesteps except the last multiplied by the duration of the timestep, divided by the total duration.

COUNT

Returns the number of timesteps under consideration for which the expression is true.

COUNT (tsr.depth2d > 1)

will return the number of timesteps under consideration for which depth2d > 1.

DURATION

Returns the time in minutes for which the expression is true for timesteps under consideration. (Timesteps are not necessarily contiguous)

EARLIEST

Returns the first non-null value of an expression. This is only likely to give a meaningful answer if used in combination with the IIF function e.g. to find the first time at which the depth is greater than 0.01

SELECT EARLIEST (IIF(tsr.depth > 0.01, tsr.depth, NULL)

FIRST

Returns the value of the expression for the first timestep under consideration.

INTEGRAL

Returns the sum of the value of the expression at each timestep under consideration multiplied by the length of the timestep in minutes.

Note that the SQL engine is not aware of the units in which values are being reported. It is the responsibility of the user to ensure that any required multiplication factor is applied.

LAST

Returns the value of the expression for the last timestep under consideration.

LATEST

Returns the last non-null value of an expression. As with EARLIEST, only likely to give a meaningful answer if used in combination with the IIF function.

MAX

Returns the maximum value for all the timesteps under consideration.

MIN

Returns the minimum value for all the timesteps under consideration.

SUM

Returns the sum of all the values at all timesteps.

Probably only useful if all timesteps are of the same length.

WHENEARLIEST

Returns the earliest time for which the expression is true e.g.

WHENEARLIEST (tsr.depnod <150)

will return the first time at which the level is less than 150

WHENLATEST

Returns the latest time for which the expression is true.

WHENMAX

Returns the time at which the expression is at its maximum.

If there is more than one timestep at which the expression is at the maximum value this will report the earliest time.

WHENMIN

Returns the time at which the expression is at its minimum.

If there is more than one timestep at which the expression is at the minimum value this will report the earliest time.

The aggregate functions EARILEST, LATEST, WHENEARLIEST, WHENLATEST, WHENMAX and WHENMIN will return dates if the simulation is using absolute times or numbers of minutes if the simulation is using relative times.

The functions AVG, INTEGRAL and SUM will not consider the final timestep in the simulation because the final timestep does not a known duration since timestep lengths can vary. This only applies to the the final timestep in the simulation, not to the final timestep under consideration.

It is possible to limit the number of time-steps used in the time varying aggregate functions by use of a WHEN clause e.g.

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_no = 20

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_start = #01/01/2013 12:30#

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_no = tsr.timesteps

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_no > 20

If a WHEN clause is used, all the above aggregate functions perform their calculations only on the time-steps selected by the WHEN clause.

The field values that can be used in WHEN clauses are as follows:

tsr.timestep_no

The number of the timestep, with 1 being the first timestep as an integer

tsr.timestep_start

The time of the start of the timestep as a date or number (see below)

tsr.timesteps

The number of timesteps in the simulation as an integer

tsr.timestep_duration

The duration of the timestep as a number of minutes

tsr.sim_start

The time of the start of the simulation as a date or number (see below)

tsr.sim_end

The time of the end of the simulation as a date or number (see below)

If the simulation uses 'relative times' then timestep_start, sim_start and sim_end return a number of minutes from the notional time 0, otherwise the date and time is returned as a 'date' value.

The expression in the WHEN clause can include the above field values along with scalar and list variables e.g. you can list a number of timesteps you are interested in and then use the list function

The results will only be processed for timesteps for which the WHEN clause is true.

The modifiers

IN BASE SCENARIO

IN CURRENT SCENARIO

IN SCENARIO '<name>'

IN SCENARIO $scalar

Can be applied as follows:

In select clauses:

SELECT IN BASE SCENARIO WHERE <expression>

SELECT IN SCENARIO 'net1' WHERE <expression>

The expression can be omitted in which case everything in that scenario is selected e.g.

SELECT IN SCENARIO 'net2'

This can be combined with the existing SELECTED and ALL keywords and the overriding of table names e.g.

SELECT SELECTED FROM [All Nodes] IN BASE SCENARIO WHERE x > 644000

'Delete' and 'Deselect' clauses behave the same way.

e.g.

DELETE ALL FROM [All Nodes] IN SCENARIO 'net2' WHERE x > 644000

DESELECT ALL FROM [All Nodes] IN SCENARIO 'net2' WHERE x < 644000

Update clauses behave the same way, with the same modifiers in conjunction with the existing SELECTED and ALL keywords and the overriding of table names e.g.

UPDATE IN SCENARIO 'net2' set user_number_1 = 123

UPDATE [All Links] IN SCENARIO 'net2' set user_number_1 = 123

UPDATE SELECTED [All Links] IN SCENARIO 'net2' set user_number_1 = 123

Variables can be used in scenarios - there is only one copy of each variable, there are no scenario dependent variables.

To set a variable based on an expression in a scenario the same syntax is used e.g.

UPDATE IN SCENARIO 'net1' SET $x = user_number_1

This provides a mechanism for comparing values in scenarios e.g.

UPDATE IN SCENARIO 'net1' SET $x = user_number_1;

SELECT WHERE $x <> user_number_1

Scenarios can be added as follows

ADD SCENARIO 'name'

ADD SCENARIO $variable

The scenario can optionally be based on another by adding

BASED ON 'name'

or

BASED ON 'variable'

Scenarios can be removed as follows

DROP SCENARIO 'name'

DROP SCENARIO $variable

The time series expressions approach to analysing time series data is very different from the approach taken for analysing time series results. In particular:

• In time series expressions, each value is an entire (finite) time series and operators and functions act on whole series. For time series results, expressions are evaluated at each time step using operators and functions that act on individual result values for that time step.
• In time series expressions, the different time series can have different time steps. For time series results, all results share the same time steps.
• Time series expressions always work with absolute times in local clock time (i.e. including daylight saving). Time series results may use relative times or absolute times in local standard time (i.e. excluding daylight saving).
• Time series expressions can construct new time series as intermediate values stored in variables, spreading the calculation over multiple semi-colon-separated clauses. For time series results, all calculations occur in the context of an aggregating function and must be carried using a single (possibly large) expression that is the argument of the aggregating function.
• Time series expressions aggregate time series using the TSAGGREGATE function, passing in the name of the desired statistic (MIN, MAX, etc.). Time series results are aggregated using separate functions for each statistic (MIN, MAX, etc.)
• Time series expressions load all the required time-series data for all network objects into memory at the same time. This is not a problem for typical analysis but could be a limitation for very dense and/or long-period data. Time series results are loaded for all objects for a single time step at a time.

The two basic operations that are required for use of time series data in InfoAsset Manager are loading of a time series (from a TSD data stream) and aggregation of a time series (to obtain a single number that can then be displayed or used in other calculations). In between, there can be any number of time series expressions to combine and transform the loaded data.

Data loading is done by the TSLOAD function, which loads data from one of the TSD streams listed in the time series links associated with an object. The link to use is identified by its description and the start and end times to load are specified explicitly. The units of measurement must also be specified explicitly. For example, if the object has a time series link named FFT that points to a TSD stream containing flow data, then data for the past week can be loaded into a variable $flow by the statement:

SET $now = NOW(); get the current time

SET $flow = TSLOAD("FFT", "m/s",$now-7,$now);

Aggregation is done by the TSAGGREGATE function, which calculates a specified statistic over a specified time period. For example, to find the maximum flow during the past day for the data loaded above:

SET $maxflow = TSAGGREGATE($flow, $now-1, $now, "MAX", 1);

A more complex and complete example would be:

Choose a period to analyse :

LET $end = NOW();

SET $start = $end - 7;

Load H2S data for this period :

SET $h2s = TSLOAD("H2S", "mg/l", $start, $end);

Get maximum H2S and time of maximum

SET $maxh2s = TSAGGREGATE($h2s, $start, $end, "max", 1.0);

SET $maxtimeh2s = TSAGGREGATE($h2s, $start, $end, "maxtime", 1.0);

We want to look at what was happening in the 3 hours before the peak H2S

SET $maxtimeh2sminus3 = $maxtimeh2s - 3.0/24.0;

Load flow data for this period and calculate the mean:

SET $prevfft = TSLOAD("FFT", "l/s", $maxtimeh2sminus3 , $maxtimeh2s);

SET $prevfftmean = TSAGGREGATE($prevfft, $maxtimeh2sminus3 , $maxtimeh2s, "mean", 1.0);

Present the results for all objects that have links to H2S data:

SELECT asset_id, DATETIMEFORMAT($maxtimeh2s ,"dd/MM/yyyy", "HH:mm") AS 'Time of Max H2S', $maxh2s AS 'Max H2S', $prevfftmean AS 'Mean FFT in previous 3 hours' WHERE $maxh2s IS NOT NULL;

Time series expressions can be used to combine and transform the loaded time series, as shown in the following (slightly contrived) example using time series links called A, B and C to streams that have no units:

LET $end = NOW();

SET $start = $end - 7;

SET $a = TSLOAD("A", "", $start, $end);

SET $b = TSLOAD("B", "", $start, $end);

SET $c = TSLOAD("C", "", $start, $end);

SET $d = 5.4 + $a + 2*$b*$b + POW($c, 3);

SET $maxd = TSAGGREGATE($d, $start, $end, "max", 1.0);

As can be seen, the time series expression looks identical to an equivalent expression that might be used to combine individual values from a single time step of each of the series. Because this expression includes whole time series, the calculations are instead carried out using the entire set of values within each time series, yielding a new time series. See Theory of time series expressions for more details.

The first phase of the process is splitting the text into keywords, field names, variable names, constants etc. If there is a problem with this stage an error message will be displayed and the processing will stop before the SQL text is broken into clauses. The message will begin ‘error parsing query:’ and will then contain a more detailed message explaining the precise problem.

In this phase the following errors may occur:

1. invalid character at start of token – the invalid character will be displayed in the message. This typically occurs if you use a character not valid in a keyword, variable name or field name. ‘:’ is an example of such a character.
2. invalid character after field separator – in invalid character will be displayed in the message. This typically occurs if you use an invalid character after the ‘.’ separating parts of a variable or field name.

The next stage is to split the text into one or more clauses separated by semi-colons. To aid understanding of subsequent error messages the clauses will be numbered consecutively from one, with the error message both indicating the clause number and containing the full text of the clause itself.

In the descriptions below, words within angular brackets stand in for other words e.g. <keyword> stands in for one of the keywords such as SELECT, DESELECT and DELETE.

In this phase the following errors may occur:

The following errors can be reported when the individual clauses are handled. To aid understanding of the errors, the number of the clause and the name of the sub clause are reported along with the clause text and the error message.

If you have a clause that selects objects e.g. x > 0, this is referred to as a WHERE sub-clause even though the keyword WHERE is not explicitly used.

Expected = after name of field to assign to

This error will occur if a field name in a SET statement is followed by something other than an equals sign e.g.

SET user_number_1 > 23

Unexpected comma

This error will typically occur if you have used commas in sub-clauses where they are not allowed e.g.

x > 0, y > 0

Expected field, got comma

This error will typically occur in an expression in brackets if you follow an operator with a comma e.g. SELECT x + (x + ,

Expecting field before comma

This error will typically occur in a SET sub-clause if you follow the = with a comma SET user_number_1 =, user_number_2 = ground_level

Got comma at beginning of expression

This error will typically occur if you have a comma after the opening bracket of a function e.g.

SET user_text_1 = LEFT(,2)

or after a parameter e.g.

SET user_text_1 = LEFT(node_id,,

Got comma when not in function

Too many function parameters

Each function has a fixed number of parameters. If too many are supplied then this error message will occur e.g. SET user_text_1 = LEFT(node_id,2,3)

Too few function parameters

On the other hand, this error message will occur if too few parameters are supplied to a function e.g. SET user_text_1 = LEFT(node_id)

More than one parameter for aggregate function

All the aggregate functions such as COUNT, MIN, MAX, AVG etc. can contain one expression which will be evaluated for all the appropriate objects (e.g. one-to-many links, array fields, objects in GROUP BY clauses). If there is more than one sub expression within the aggregate function, this error message will occur e.g. SET user_text_1 = COUNT(x,y)

Too many levels of aggregate functions

In GROUP BY clauses it is, as described above, possible to use aggregate functions on the results of aggregate functions – this is because you are aggregating the results of the aggregate function on each object over a group of objects. This is the only case in which calling an aggregate function on the result of an aggregate function has a legitimate meaning, so if you attempt to say something like

COUNT(COUNT(details.*)) or

SELECT COUNT(COUNT(COUNT(details.*))) GROUP BY direction this error message will occur.

Unrecognised function - <functionname>

If an expression resembles a function, but the name of the function is not one of those available, this error message will occur e.g. length(node_id)

Too many close brackets

This error message will occur if you have more close brackets than open brackets e.g. direction = 'U')

Empty parenthesis

This error message will occur if open and close brackets are next to each other like this ‘()’ except in the rare case of a function with no parameters (of which there is currently only one, NOW, as described above) e.g. direction + ()

<token> : expected field or function

This error message will typically occur if you have two consecutive operators e.g. cover_level-*ground_level

You are, of course, allowed to use the unary minus operator so

cover_level*-ground_level

is a valid expression.

Functions cannot be assigned to

You cannot assign values to functions, so something like

SET LEN(node_id)=10

is an error.

Aggregate functions cannot be assigned to

Similarly, you cannot assign values to aggregate functions, so something like

SET COUNT(details.*) = 10

is an error.

You can only assign to fields and functions

This error will occur if you attempt to assign a value to a constant e.g. SET 2 + 2 = 5

AS cannot appear at the end of a SELECT sub-clause

If you use the keyword AS immediately before the end of the SELECT sub-clause in a GROUP BY clause, rather than giving an alias name, this error message will result e.g.

SELECT COUNT(*) AS GROUP BY status

‘<text>’ : invalid name for alias after AS keyword

This error message will result if something unsuitable e.g. a number is used after the keyword AS in the SELECT sub-clause of a GROUP BY clause e.g.

SELECT COUNT(*) AS 23 GROUP BY status

Other unsuitable aliases are variables, strings in quotes

SELECT COUNT(*) AS $variable GROUP BY status

SELECT COUNT(*) AS “my count” GROUP BY status

Only a comma or the end of the clause can appear after the alias after the AS keyword

This error message will result if you attempt to place anything other than a comma after the alias of an expression in the SELECT sub-clause of a GROUP BY clause e.g. SELECT COUNT(*) AS [My Count] * 2 GROUP BY status

The following error messages will be displayed if variables and/or field names are invalid. At this stage it is possible that more than one error message will be displayed at once.

<fieldname> is not a recognized field name

This simply means that the field name is not recognized as being a field for the current table.

<fieldname> - * used in an invalid context

As described above, * may only be used in a number of specific contexts, as follows:

COUNT(*)

ANY(<something>.*)

COUNT(<something>.*)

where something is the name of an array field or a one-to-many link. If an attempt is made to use it in some other context then this error message will result.

You can of course use * outside variable names to multiply numbers together.

variable <name> is not a list variable but is being used in a list context

As described above, list variables can only be used as the only or final parameter in a number of functions. Those functions can only take a list variable as the final parameter and you attempt to use something else as that final parameter this error message will result.

variable <name> has not been defined but is being used in a list context

As described above, variables are created the first time they are used. However, when they are used in a function expecting a list variable as its only or final parameter, they must have been previously defined in a LIST clause. If you try to use a variable for the first time as the final or only parameter of a function expecting a list variable, this error message will result.

variable <name> is a list variable but is being used in another context

This is the converse of the above message, and results if you attempt to use a list variable in another context e.g. if it has been set up for nodes and is then used for links.

variable <name> has already been used in a different context

This message results if you attempt to use a variable that has already been used for one object type in another.

<something> is not recognized as a valid date

If you use a date constant within a query e.g. SET survey_date = #1/2/2008# the date string will be checked before the query is run, and if it is invalid e.g. SET survey_date = #13/13/2007# this message will be displayed.

The following notes may help you understand the nature of the error messages you are seeing.

1. If you forget to use the dollar sign at the beginning of a variable name in a LIST clause, the software will treat this as a normal clause without any keywords and produce an appropriate error message for that.
2. If you have a query that only defines list variables, when you hit the ‘test’ button the message ‘Query has valid syntax, but not actions will be taken’ will be displayed.